IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_PageSelectParentPages]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_PageSelectParentPages]
GO
create procedure [dbo].[cms_sp_PageSelectParentPages]
(
	@SiteGroupId int = null,
	@SiteId int = null,
	@PageId int,
	@IncludeStartPage bit,
	@IsInAdministration bit = null,
	@PageTypeParameter nvarchar(400)
)
as
if @IsInAdministration is null
begin
	set @IsInAdministration = 0
end
declare @PageNodeId hierarchyid
declare @IsPublished bit
set @IsPublished = 1
select 
	@PageNodeId = PageNodeId
from dbo.Pages
inner join [dbo].[EntityRevisions]
	on EntityRevisions.EntityId = Pages.PageId and 
	EntityRevisions.EntityTypeParameter = @PageTypeParameter
where ((SiteGroupId is null and SiteId is null) or 
	(SiteGroupId = @SiteGroupId and SiteId is null) or
	(SiteGroupId = @SiteGroupId and SiteId = @SiteId)) and 
	EntityRevisions.IsLatestCompletedRevision = 1 and
	EntityRevisions.IsPublished = @IsPublished and
	EntityRevisions.IsDeleted = 0 and
	Pages.IsVisible = 1 and PageId = @PageId
select 
	Pages.PageId,
	Pages.PublicPageId,
	ParentPages.PageId as ParentPageId,
	Pages.SiteGroupId,
	Pages.SiteId,
	Pages.SystemLanguageId,	
	Pages.PageTypeKey,
	Pages.PageBehaviourTypeKey,
	Pages.PageNodeId.GetLevel() as PageLevel,
	ChildrenPages.ChildCount as ChildrenCount,
	Pages.PageName,
	Pages.PageUrl,
	Pages.IsVisible,
	Pages.UseFileCache,
	Pages.CreatedBy,
	Pages.DateCreated,
	Pages.LastUpdatedBy,
	Pages.DateLastUpdated,
	Pages.PageKey,
	Creators.UserName as UserCreatedBy,
	Updaters.UserName as UserLastUpdatedBy,
	EntityRevisions.IsPublished,
	EntityRevisions.PublishStartDate,
	EntityRevisions.PublishEndDate,
	EntityRevisions.IsLatestRevision,
	EntityRevisions.IsLatestCompletedRevision,
	EntityRevisions.IsDeleted,
	t.TemplateName as Template_TemplateName,
	t.TemplateId as Template_TemplateId,
	t.DefaultRegionId as Template_DefaultRegionId
from dbo.Pages
inner join Templates t on t.TemplateId = Pages.TemplateId
left join(select ChildPages.PageId, ChildPages.PageNodeId
		  from dbo.Pages as ChildPages
		  inner join EntityRevisions on EntityRevisions.EntityId = ChildPages.PageId and
					 EntityRevisions.EntityTypeParameter = @PageTypeParameter and
					((@IsInAdministration = 1 and EntityRevisions.IsLatestRevision = 1) or
					(@IsInAdministration = 0 and EntityRevisions.IsLatestCompletedRevision = 1)) and	
					((@IsInAdministration = 1) or (@IsInAdministration = 0 and EntityRevisions.IsPublished = 1))
		  where	((ChildPages.[SiteGroupId] is null and ChildPages.[SiteId] is null) or 
				(ChildPages.[SiteGroupId] = @SiteGroupId and ChildPages.[SiteId] is null) or
				(ChildPages.[SiteGroupId] = @SiteGroupId and ChildPages.[SiteId] = @SiteId))					
		 ) as ParentPages on ParentPages.PageNodeId = Pages.PageNodeId.GetAncestor(1)
left join(select count(ChildPages.PageNodeId) as ChildCount, PageNodeId.GetAncestor(1) as PageNodeId
		  from dbo.Pages as ChildPages
		  inner join EntityRevisions on EntityRevisions.EntityId = ChildPages.PageId and
					 EntityRevisions.EntityTypeParameter = @PageTypeParameter and
					((@IsInAdministration = 1 and EntityRevisions.IsLatestRevision = 1) or
					(@IsInAdministration = 0 and EntityRevisions.IsLatestCompletedRevision = 1)) and	
					((@IsInAdministration = 1) or (@IsInAdministration = 0 and EntityRevisions.IsPublished = 1))
		  where	((ChildPages.[SiteGroupId] is null and ChildPages.[SiteId] is null) or 
				(ChildPages.[SiteGroupId] = @SiteGroupId and ChildPages.[SiteId] is null) or
				(ChildPages.[SiteGroupId] = @SiteGroupId and ChildPages.[SiteId] = @SiteId))					
		  group by PageNodeId.GetAncestor(1)
		  ) as ChildrenPages on ChildrenPages.PageNodeId = Pages.PageNodeId		
inner join [dbo].[EntityRevisions]
	on EntityRevisions.EntityId = Pages.PageId and 
	EntityRevisions.EntityTypeParameter = @PageTypeParameter
left join [dbo].[Users] as Creators
	on Creators.UserId = Pages.CreatedBy
left join [dbo].[Users] as Updaters
	on Updaters.UserId = Pages.LastUpdatedBy
where 
	((Pages.SiteGroupId is null and Pages.SiteId is null) or 
	(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId is null) or
	(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId = @SiteId)) and 
	EntityRevisions.IsLatestCompletedRevision = 1 and
	EntityRevisions.IsPublished = @IsPublished and 
	SYSDATETIME() between COALESCE(EntityRevisions.PublishStartDate, '1900-01-01') and COALESCE(EntityRevisions.PublishEndDate, '2050-01-01') and
	EntityRevisions.IsDeleted = 0 and
	Pages.IsVisible = 1 and
	@PageNodeId.IsDescendantOf(Pages.PageNodeId) = 1 and
	((@IncludeStartPage = 1 and Pages.PageNodeId.GetLevel() <= @PageNodeId.GetLevel()) or
	(@IncludeStartPage = 0 and Pages.PageNodeId.GetLevel() < @PageNodeId.GetLevel()))
order by Pages.PageNodeId
GO